package com.newnius.picbrowser;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
/**
 *
 * @author Newnius
 */


public class SQLManager {

    static String db = "jdbc:derby://localhost:1527/helloDB;create=true";
    static String user = "newnius";
    static String pwd = "123456";
    static String table = "picbrowser";

    public static boolean createTable() {
        boolean res = false;
        try {
            Class.forName("org.apache.derby.jdbc.ClientDriver");
            Connection conn = DriverManager.getConnection(db, user, pwd);
            conn.setAutoCommit(false);
            Statement s = conn.createStatement();
            String sql = "create table picbrowser(id int primary key generated by default as identity,url varchar(50) not null unique,title varchar(50) not null default 'empty title')";
            System.out.println(sql);
            res = s.execute(sql);
            s.close();
            conn.commit();
            conn.close();

        } catch (ClassNotFoundException e) {
            System.out.println("wrong at line:30");
        } catch (SQLException e) {
            System.out.println("wrong at line:33");
        } catch (Exception e) {
            System.out.println("wrong at line:34");
        }
        return res;
    }

    public static ArrayList<MyUrl> init() {
        ArrayList<MyUrl> list = new ArrayList<>();
        try {
            Class.forName("org.apache.derby.jdbc.ClientDriver");
            Connection conn = DriverManager.getConnection(db, user, pwd);
            conn.setAutoCommit(false);
            ResultSet rs = conn.getMetaData().getTables(null, null, table.toUpperCase(), null);
            if (rs.next()) {
                //yourTable exist
                Statement s = conn.createStatement();
                String sql = "SELECT id,url,title FROM " + table + " ORDER BY id";
                System.out.println(sql);
                ResultSet res = s.executeQuery(sql);

                while (res.next()) {
                    list.add(new MyUrl(res.getInt(1), res.getString(2), res.getString(3)));
                }
                s.close();
                conn.commit();
                conn.close();
            } else {
                conn.commit();
                conn.close();
                if (createTable() == false) {
                    System.out.println("wrong at line:608");
                }
            }

        } catch (ClassNotFoundException e) {
            System.out.println("wrong at line:68");
        } catch (SQLException e) {
            System.out.println("wrong at line:70");
        } catch (Exception e) {
            System.out.println("wrong at line:73");
        }
        return list;
    }

    public static String getUrl(int tmpID) {
        String localUrl = "";
        try {
            Class.forName("org.apache.derby.jdbc.ClientDriver");
            Connection conn = DriverManager.getConnection(db, user, pwd);
            conn.setAutoCommit(false);
            ResultSet rs = conn.getMetaData().getTables(null, null, table.toUpperCase(), null);
            if (rs.next()) {
                //yourTable exist
                Statement s = conn.createStatement();
                String sql = "SELECT url FROM " + table + " WHERE id=" + tmpID + "";
                System.out.println(sql);
                ResultSet res = s.executeQuery(sql);
                while (res.next()) {
                    localUrl = res.getString(1);
                }
                s.close();
                conn.commit();
                conn.close();
            } else {
                conn.commit();
                conn.close();
                if (createTable() == false) {
                    System.out.println("wrong at line:645");
                }
            }
        } catch (ClassNotFoundException e) {
            System.out.println("wrong at line:104");
        } catch (SQLException e) {
            System.out.println("wrong at line:106");
        } catch (Exception e) {
            System.out.println("wrong at line:108");
        }
        return localUrl;
    }

    public static boolean delUrl(int tmpID) {
        boolean res = false;
        try {
            Class.forName("org.apache.derby.jdbc.ClientDriver");
            Connection conn = DriverManager.getConnection(db, user, pwd);
            conn.setAutoCommit(false);
            ResultSet rs = conn.getMetaData().getTables(null, null, table.toUpperCase(), null);
            if (rs.next()) {
                //yourTable exist
                Statement s = conn.createStatement();
                String sql = "DELETE FROM " + table + " WHERE id='" + tmpID + "'";
                System.out.println(sql);
                res = s.execute(sql);

                s.close();
                conn.commit();
                conn.close();
            } else {
                conn.commit();
                conn.close();
                if (createTable() == false) {
                    System.out.println("wrong at line:678");
                }
            }
        } catch (ClassNotFoundException e) {
            System.out.println("wrong at line:138");
        } catch (SQLException e) {
            System.out.println("wrong at line:140");
        } catch (Exception e) {
            System.out.println("wrong at line:142");
        }

        return res;
    }

    public static boolean addUrl(String tmpUrl, String tmpTitle) {
        boolean res = true;
        try {
            Class.forName("org.apache.derby.jdbc.ClientDriver");
            Connection conn = DriverManager.getConnection(db, user, pwd);
            conn.setAutoCommit(false);
            ResultSet rs = conn.getMetaData().getTables(null, null, table.toUpperCase(), null);
            if (rs.next()) {
                try ( //yourTable exist
                        Statement s = conn.createStatement()) {
                    String sql = "INSERT INTO " + table + "(url,title) VALUES('" + tmpUrl + "','" + tmpTitle + "')";
                    System.out.println(sql);
                    s.execute(sql);
                } catch (Exception e) {
                    System.out.println("wrong at line:711;already exists");
                    res=false;
                }
                conn.commit();
                conn.close();
            } else {
                conn.commit();
                conn.close();
                if (createTable() == false) {
                    System.out.println("wrong at line:712");
                }
            }
        } catch (ClassNotFoundException e) {
            System.out.println("wrong at line:174");
        } catch (SQLException e) {
            System.out.println("wrong at line:176");

        } catch (Exception e) {
            System.out.println("wrong at line:179");
        }
        return res;
    }

}

class MyUrl {
    int id;
    String url;
    String title;

    public MyUrl(int tmpID, String tmpUrl, String tmpTitle) {
        id = tmpID;
        url = tmpUrl;
        title = tmpTitle;
    }
}
